﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Security.Cryptography;
using System.Net;
using System.Net.Mail;
using System.Text;
using System.Numerics;

namespace eVoting
{
    public class DBManager
    {
        DBConnectionDataContext context;

        public DBManager()
        {
            context = new DBConnectionDataContext();
        }



        //User(Student) Manager :

        public List<User> getAllUsers()
        {
            var usersQuery = from user in context.Users
                             select user;
            return usersQuery.ToList();
        }

        public List<User> getAllNonAdvisingStudents()
        {
            var usersQuery = from user in context.Users
                             where user.isAdvising == false
                                || user.isAdvising == null
                             select user;
            return usersQuery.ToList();
        }

        public User getUserById(int userID)
        {
            var userQuery = from user in context.Users
                            where user.ID == userID
                            select user;
            try { return userQuery.Single(); }
            catch { return null; }
        }

        public User getUserByGucId(string gucID)
        {
            var userQuery = from user in context.Users
                            where user.gucID == gucID
                            select user;
            try { return userQuery.Single(); }
            catch { return null; }
        }

        public User getUserByMail(string email)
        {
            var userQuery = from user in context.Users
                            where user.email == email
                            select user;
            try { return userQuery.Single(); }
            catch { return null; }
        }

        public List<User> getUsersByStudyGroup(int sgID)
        {
            var usersQuery = from user in context.Users
                             join studyGroup in context.StudyGroups
                             on user.studyGroup equals studyGroup.ID
                             where studyGroup.ID == sgID
                             select user;
            return usersQuery.ToList();
        }

        public List<User> getUsersByFaculty(int facultyID)
        {
            var usersQuery = from user in context.Users
                             join studyGroup in context.StudyGroups
                             on user.studyGroup equals studyGroup.ID
                             join faculty in context.Faculties
                             on studyGroup.faculty equals faculty.ID
                             where faculty.ID == facultyID
                             select user;
            return usersQuery.ToList();
        }


        public int getNumOfVotersByPollId(int pollID)
        {
            List<StudyGroup> SGs = getAssignedStudyGroupsByPollId(pollID);
            int num = 0;
            foreach (StudyGroup sg in SGs)
            {
                try { num += getUsersByStudyGroup(sg.ID).Count; }
                catch { }
            }

            return num;
        }

        public List<StudyGroup> getAssignedStudyGroupsByPollId(int pollID)
        {
            var sgQuery = from poll in context.Polls
                          where poll.ID == pollID
                          join pollSG in context.PollStudyGroups
                          on poll.ID equals pollSG.poll
                          select pollSG.StudyGroup1;
            return sgQuery.ToList();
        }

        public List<Poll> getAssignedPollsByUserID(int userID)   //addeddddddddd
        {
            var usersQuery = from user in context.Users
                             join pollStudyGroup in context.PollStudyGroups
                             on user.studyGroup equals pollStudyGroup.studyGroup
                             join poll in context.Polls
                             on pollStudyGroup.poll equals poll.ID
                             where userID == user.ID && user.isBanned == false && poll.startDate <= DateTime.Today && poll.endDate > DateTime.Today
                             select poll;
            return usersQuery.ToList();
        }

        public bool createNewUser(string gucID, string firstName, string middleName, string lastName, string email, string password, string salt, int studyGroupID, bool? isAdvising)
        {
            User newUser = new User();
            newUser.gucID = gucID;
            newUser.firstName = firstName;
            newUser.middleName = middleName;
            newUser.lastName = lastName;
            newUser.email = email;
            newUser.password = password;
            newUser.studyGroup = studyGroupID;
            newUser.isAdvising = isAdvising;
            newUser.pwSalt = salt;
            newUser.isBanned = false;
            newUser.isConfirmed = false;
            newUser.isAdvising = false;
            newUser.pwDate = DateTime.Now;

            context.Users.InsertOnSubmit(newUser);
            try { context.SubmitChanges(); return true; }
            catch { return false; }
        }

        public void confirmUser(int userID)
        {
            try
            {
                User user = getUserById(userID);
                user.isConfirmed = true;
                context.SubmitChanges();
            }
            catch { }
        }

        public void banUserFromVoting(int userID)
        {
            try
            {
                User user = getUserById(userID);
                user.isBanned = true;
                context.SubmitChanges();
            }
            catch { }
        }

        public void markUserAsAdvising(int userID)
        {
            try
            {
                User user = getUserById(userID);
                user.isAdvising = true;
                context.SubmitChanges();
            }
            catch { }
        }



        //*******************


        //Candidate Manager :


        public List<BigInteger> getVotes(int pollID)
        {
            List<PollUser> pollUsers = getUsersVotedInPoll(pollID);

            List<BigInteger> votes = new List<BigInteger>();

            foreach (PollUser pu in pollUsers)
            {
                votes.Add(BigInteger.Parse(pu.candidate));
            }

            return votes;
        }

        public List<Candidate> getCandidatesByPoll(int pollID)
        {
            var candidatesQuery = from candidate in context.Candidates
                                  where candidate.poll == pollID
                                  select candidate;
            return candidatesQuery.ToList();
        }

        public Candidate createNewCandidate(string firstName, string middleName, string lastName, int pollID)
        {
            int currentCandidates = getNumOfCandidatesByPollId(pollID);
            int maxCandidates = getPollById(pollID).numOfCandidates;

            if (currentCandidates >= maxCandidates)
                return null;

            Candidate myCandidate = new Candidate();
            myCandidate.firstName = firstName;
            myCandidate.middleName = middleName;
            myCandidate.lastName = lastName;
            myCandidate.Poll1 = getPollById(pollID);
            myCandidate.voteCount = -1;

            context.Candidates.InsertOnSubmit(myCandidate);


            try { context.SubmitChanges(); return myCandidate; }
            catch { return null; }
        }
        
        public int getNumOfCandidatesByPollId(int pollID)
        {
            return getCandidatesByPoll(pollID).Count;
        }


        //**************


        //StudyGroup Manager :

        public StudyGroup getStudyGroup(int facultyID, int academicYear)
        {
            var sgQuery = from sg in context.StudyGroups
                          where sg.faculty == facultyID
                          where sg.year == academicYear
                          select sg;

            try { return sgQuery.Single(); }
            catch { return null; }
        }

        public List<StudyGroup> getAllStudyGroups()
        {
            var sgsQuery = from sg in context.StudyGroups
                           select sg;
            return sgsQuery.ToList();
        }

        public void updateStudyGroupForAllNonAdvisingStudents(int currentYear)
        {
            List<User> nonAdvisingStudents = getAllNonAdvisingStudents();

            foreach (User student in nonAdvisingStudents)
            {
                int academicYear = getAcademicYear(student.gucID, currentYear);
                StudyGroup newSG = getStudyGroup(student.StudyGroup1.faculty, academicYear);

                student.StudyGroup1 = newSG;

                context.SubmitChanges();
            }
        }

        //*********************

        // Poll Manager :

        public Poll createNewPoll(string name, string description, DateTime startDate, DateTime endDate, int numOfCandidates)
        {
            Poll myPoll = new Poll();
            myPoll.name = name;
            myPoll.description = description;
            myPoll.startDate = startDate;
            myPoll.endDate = endDate;
            myPoll.numOfCandidates = numOfCandidates;

            context.Polls.InsertOnSubmit(myPoll);
            try { context.SubmitChanges(); return myPoll; }
            catch { return null; }
        }

        public Poll getPollById(int pollID)
        {
            var pollQuery = from poll in context.Polls
                            where poll.ID == pollID
                            select poll;
            try { return pollQuery.Single(); }
            catch { return null; }
        }

        public List<Poll> getAllPolls()
        {
            var pollQuery = from poll in context.Polls
                            select poll;
            return pollQuery.ToList();
        }

        public bool assignPollToStudyGroup(int pollID, int sgID)
        {
            PollStudyGroup ps = new PollStudyGroup();
            ps.poll = pollID;
            ps.studyGroup = sgID;

            context.PollStudyGroups.InsertOnSubmit(ps);
            try { context.SubmitChanges(); return true; }
            catch { return false; }

        }

        public bool assignPollToStudyGroup(Poll poll, StudyGroup studyGroup)
        {
            PollStudyGroup ps = new PollStudyGroup();
            ps.Poll1 = poll;
            ps.StudyGroup1 = studyGroup;

            context.PollStudyGroups.InsertOnSubmit(ps);
            try { context.SubmitChanges(); return true; }
            catch { return false; }
        }

        public bool assignPollToStudyGroups(int pollID, int[] sgIDs)
        {
            foreach (int sgID in sgIDs)
            {
                PollStudyGroup ps = new PollStudyGroup();
                ps.poll = pollID;
                ps.studyGroup = sgID;

                context.PollStudyGroups.InsertOnSubmit(ps);
            }

            try { context.SubmitChanges(); return true; }
            catch { return false; }
        }

        public bool assignPollToStudyGroups(Poll poll, List<StudyGroup> studyGroups)
        {
            foreach (StudyGroup studyGroup in studyGroups)
            {
                PollStudyGroup ps = new PollStudyGroup();
                ps.Poll1 = poll;
                ps.StudyGroup1 = studyGroup;

                context.PollStudyGroups.InsertOnSubmit(ps);
            }

            try { context.SubmitChanges(); return true; }
            catch { return false; }
        }

        //*********************

        public List<Faculty> getAllFaculties()
        {
            var facQuery = from faculty in context.Faculties
                           select faculty;
            return facQuery.ToList();
        }


        public Faculty getFacultyById(int facultyID)
        {
            var facQuery = from faculty in context.Faculties
                           where faculty.ID == facultyID
                           select faculty;
            try { return facQuery.Single(); }
            catch { return null; }
        }

        public Faculty getFacultyByAbb(string abb)
        {
            var facQuery = from faculty in context.Faculties
                           where faculty.abbreviation == abb
                           select faculty;
            try { return facQuery.Single(); }
            catch { return null; }
        }

        public Faculty getFacultyByName(string facultyName)
        {
            var facQuery = from faculty in context.Faculties
                           where faculty.name == facultyName
                           select faculty;
            try { return facQuery.Single(); }
            catch { return null; }
        }

        public int getFacultyIdByName(string facultyName)
        {
            var facQuery = from faculty in context.Faculties
                           where faculty.name == facultyName
                           select faculty.ID;
            try { return facQuery.Single(); }
            catch { return 0; }
        }



        //public DateTime getPWDate(int userID)
        //{
        //    User user = getUserById(userID);

        //    return user.;
        //}


        //public void changePW(int userID, string pw)
        //{
        //   User user = getUserById(userID);

        //            user.password = pw;

        //          user.pwDate = DateTime.Now;

        //        context.SubmitChanges();
        //  }

        public List<Candidate> getPollResults(int pollId)
        {
            var query = from poll in context.Polls
                        join candidates in context.Candidates
                        on poll.ID equals candidates.poll
                        where poll.ID == pollId && candidates.voteCount != -1
                        select candidates;
            return query.ToList();

        }

        // Helper Methods :

        private int getAcademicYear(string gucID, int currentYear)
        {
            int beforeDash = Int32.Parse(gucID.Split('-')[0]);
            int classNum = (int)(beforeDash + 2) / 3;
            int academicYear = currentYear - classNum - 2001;
            return academicYear;
        }




        public bool changePW(string newPW, string newSalt, User user)
        {
            user.password = newPW;
            user.pwSalt = newSalt;
            user.pwDate = DateTime.Now;
            try
            {
                context.SubmitChanges();
                return true;
            }
            catch { return false; }
        }


        //Non-DB Methods

        public string CreatePasswordHash(string pwd, string salt)
        {
            string saltAndPwd = String.Concat(pwd, salt);
            string hashedPwd = FormsAuthentication.HashPasswordForStoringInConfigFile(saltAndPwd, "sha1");

            return hashedPwd;
        }

        public string CreateSalt(int size)
        {
            //Generate a cryptographic random number.
            RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
            byte[] buff = new byte[size];
            rng.GetBytes(buff);
            return Convert.ToBase64String(buff);
        }

        public bool insertPollUser(int userID, int pollID, string candidateID) //addeedddddd
        {
            PollUser newPollUserEntry = new PollUser();
            newPollUserEntry.user = userID;
            newPollUserEntry.poll = pollID;
            newPollUserEntry.candidate = candidateID;
            context.PollUsers.InsertOnSubmit(newPollUserEntry);
            try { context.SubmitChanges(); return true; }
            catch { return false; }
        }

        public List<PollUser> getUsersVotedInPoll(int pollID) //newww added
        {
            var usersQuery = from pollUser in context.PollUsers
                             where pollUser.poll == pollID
                             select pollUser;
            return usersQuery.ToList();
        }

        public List<Poll> getUserTerminatedPolls(int userID) //addedddd
        {
            var usersQuery = from user in context.Users
                             join pollStudyGroup in context.PollStudyGroups
                             on user.studyGroup equals pollStudyGroup.studyGroup
                             join poll in context.Polls
                             on pollStudyGroup.poll equals poll.ID
                             orderby poll.endDate descending
                             where userID == user.ID && poll.endDate <= DateTime.Today
                             select poll;
            return usersQuery.ToList();
        }

        public bool hadVoted(int userID, int pollID) //addeeeed
        {
            var usersQuery = from polUser in context.PollUsers
                             where polUser.user == userID && polUser.poll == pollID
                             select polUser.user;

            if (usersQuery.ToList().Count != 0)
            { return true; }
            else
            { return false; }

        }

        public void updateCandidatesCount(int pollId, List<int> counts)
        {
            List<Candidate> candidates = getCandidatesByPoll(pollId);

            int index = counts.Count - 1;

            foreach(Candidate can in candidates)
            {
                can.voteCount = counts[index];
                context.SubmitChanges();
                index--;
            }
        }

        public void sendMail(string address, string message)
        {
            SmtpClient ss = new SmtpClient("smtp.gmail.com", 587);
            ss.UseDefaultCredentials = false;
            ss.Credentials = new NetworkCredential("guc.su.evoting@gmail.com", "gucevoting123");
            ss.EnableSsl = true;
            //ss.Timeout = 10000;
            ss.DeliveryMethod = SmtpDeliveryMethod.Network;
            MailAddress maf = new MailAddress("guc.su.evoting@gmail.com", "GUC-Student Union eVoting");
            MailMessage mm = new MailMessage(maf, new MailAddress(address));
            mm.Subject = "Your poll vote";
            mm.Body = message;

            mm.BodyEncoding = UTF8Encoding.UTF8;
            mm.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;
            ss.Send(mm);

        }

        public void sendConfirmationMail(User user)
        {
            string address = user.email+"@student.guc.edu.eg";
            SmtpClient ss = new SmtpClient("smtp.gmail.com", 587);
            ss.UseDefaultCredentials = false;
            ss.Credentials = new NetworkCredential("guc.su.evoting@gmail.com", "gucevoting123");
            ss.EnableSsl = true;
            //ss.Timeout = 10000;
            ss.DeliveryMethod = SmtpDeliveryMethod.Network;

            MailMessage mm = new MailMessage("guc.su.evoting@gmail.com", address, "Account Confirmation", "http://localhost:49161/Confirm.aspx?id="+user.ID);
    

            mm.BodyEncoding = UTF8Encoding.UTF8;
            mm.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;
            ss.Send(mm);

        }
    }


}
